Перейти к основному содержимому

3.07. Сложные индексы

В РАЗРАБОТКЕНЕ ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНО

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Сложные индексы​

Сложные индексы представляют собой расширенные механизмы ускорения доступа к данным в реляционных базах данных. В отличие от простых индексов, построенных по одному столбцу, сложные индексы охватывают несколько столбцов одновременно, а также могут включать дополнительные данные, специальные структуры хранения или особые алгоритмы обработки запросов. Их применение позволяет значительно повысить производительность выполнения запросов, особенно в сценариях, где требуется фильтрация, сортировка или группировка по комбинации полей.

Эффективное использование сложных индексов требует понимания внутреннего устройства систем управления базами данных, особенностей оптимизатора запросов и характеристик рабочей нагрузки приложения. Неправильно спроектированный индекс может не только не принести пользы, но и замедлить операции вставки, обновления и удаления, поскольку каждое изменение данных требует синхронного обновления всех связанных индексов.

Многостолбцовые индексы​

Многостолбцовый индекс — это индекс, построенный сразу по двум или более столбцам одной таблицы. Такой индекс организован как древовидная структура, в которой ключи сортируются сначала по первому указанному столбцу, затем по второму, и так далее. Порядок указания столбцов в определении индекса имеет критическое значение для его эффективности.

Оптимизатор запросов может использовать многостолбцовый индекс в следующих случаях:

  • Когда условие WHERE содержит предикаты по всем столбцам индекса.
  • Когда предикаты заданы только по начальным столбцам индекса (например, по первому, или по первому и второму).
  • Когда запрос включает сортировку ORDER BY по тем же столбцам и в том же порядке, что и в индексе.
  • Когда запрос выполняет группировку GROUP BY по начальным столбцам индекса.

Если запрос фильтрует данные только по второму или последующему столбцу индекса, без указания условия по первому столбцу, то большинство СУБД не смогут воспользоваться таким индексом напрямую. Это связано с тем, что значения второго столбца внутри индекса не упорядочены глобально — они упорядочены только в рамках значений первого столбца.

При проектировании многостолбцового индекса рекомендуется располагать столбцы в порядке убывания их селективности — то есть от наиболее различающих значений к наименее. Также важно учитывать частоту использования тех или иных комбинаций столбцов в реальных запросах. Индекс, соответствующий типичным шаблонам обращения к данным, обеспечивает максимальную выгоду.

Покрывающие индексы​

Покрывающий индекс — это индекс, который содержит все столбцы, необходимые для выполнения конкретного запроса. Благодаря этому оптимизатор может полностью удовлетворить запрос, используя только данные из индекса, без необходимости обращения к самой таблице. Такой подход называется «индексный поиск без обращения к строке» (index-only scan).

Преимущество покрывающего индекса заключается в снижении количества операций чтения с диска или из памяти. Даже если данные таблицы находятся в кэше, чтение из индекса обычно быстрее, поскольку индекс компактнее и лучше помещается в буферный пул. Особенно заметна выгода при работе с большими таблицами, где строки занимают значительный объём памяти.

В некоторых СУБД, таких как PostgreSQL, покрывающий индекс можно создать с помощью конструкции INCLUDE. Эта конструкция позволяет добавить в индекс дополнительные неключевые столбцы, которые не участвуют в сортировке, но могут быть использованы для покрытия запроса. Например:

CREATE INDEX idx_orders_customer_date_include_total
ON orders (customer_id, order_date)
INCLUDE (total_amount);

В этом случае индекс упорядочен по customer_id и order_date, а total_amount хранится в листовых узлах индекса, но не влияет на порядок. Запрос вида:

SELECT total_amount
FROM orders
WHERE customer_id = 123 AND order_date >= '2025-01-01';

может быть выполнен только за счёт этого индекса, без обращения к таблице orders.

В других СУБД, например в Microsoft SQL Server, покрывающий индекс создаётся с помощью предложения INCLUDE аналогичным образом. В MySQL покрытие достигается путём включения всех необходимых столбцов непосредственно в список ключевых полей индекса, что может увеличить его размер, но сохраняет функциональность.

Покрывающие индексы особенно полезны для часто выполняемых запросов, возвращающих небольшой набор столбцов, но фильтрующих по другим. Они позволяют избежать дорогостоящих операций случайного доступа к строкам таблицы и ускоряют выполнение даже при высокой нагрузке.

Обобщённые инвертированные индексы (GIN) в PostgreSQL​

Обобщённый инвертированный индекс (Generalized Inverted Index, GIN) — это специализированная структура индекса, реализованная в PostgreSQL для эффективной работы с составными и нескалярными типами данных, такими как массивы, JSON, полнотекстовый поиск и геометрические объекты.

В отличие от B-дерева, где каждый ключ в индексе соответствует одной строке таблицы, в GIN один элемент данных (например, значение внутри массива) может быть связан с множеством строк. Индекс строится как «инвертированная» карта: для каждого возможного значения хранится список идентификаторов строк, в которых это значение встречается.

Такая структура идеально подходит для запросов, проверяющих наличие определённого элемента в составном значении. Например, если в таблице есть столбец tags типа TEXT[], и нужно найти все записи, содержащие тег 'database', GIN-индекс позволит мгновенно получить список подходящих строк без полного сканирования таблицы.

Пример создания GIN-индекса для массива:

CREATE INDEX idx_articles_tags_gin ON articles USING GIN (tags);

Запрос:

SELECT * FROM articles WHERE 'database' = ANY(tags);

будет использовать этот индекс и выполняться значительно быстрее, чем без него.

GIN также применяется для полнотекстового поиска. При индексировании tsvector-колонки GIN обеспечивает высокую скорость поиска документов по ключевым словам. Например:

CREATE INDEX idx_documents_fts ON documents USING GIN (to_tsvector('russian', content));

После этого запрос:

SELECT title FROM documents
WHERE to_tsvector('russian', content) @@ to_tsquery('russian', 'технологии & разработка');

выполняется с использованием GIN-индекса.

Особенностью GIN является то, что он менее эффективен при частых операциях вставки и обновления, поскольку каждое изменение составного значения требует обновления нескольких записей в индексе. Поэтому GIN лучше всего подходит для таблиц с преобладанием операций чтения или для сценариев, где обновления происходят пакетно.

PostgreSQL предоставляет гибкие возможности настройки GIN через параметры fastupdate и gin_pending_list_limit, позволяя балансировать между скоростью вставки и скоростью поиска. В высоконагруженных OLTP-системах такие настройки могут существенно повлиять на общую производительность.

Стратегии индексирования в бэкенде​

Эффективное индексирование в бэкенд-системах — это не просто техническая задача, а часть общей архитектурной стратегии. Проектирование индексов должно учитывать характер рабочей нагрузки приложения, частоту и типы запросов, объём данных, требования к задержкам и пропускной способности, а также стоимость операций модификации данных.

В высоконагруженных системах, где миллионы пользователей одновременно читают и пишут данные, неправильно выбранные индексы могут стать узким местом. Поэтому разработчики и администраторы баз данных применяют комплексные подходы к управлению индексами на всех этапах жизненного цикла приложения.

Анализ запросов и профилирование​

Первый шаг в построении эффективной стратегии индексирования — это сбор и анализ реальных запросов. Большинство СУБД предоставляют инструменты для логирования медленных запросов, просмотра планов выполнения (execution plans) и мониторинга использования индексов. На основе этих данных можно выявить «тяжёлые» запросы, которые выполняются без использования индексов или используют их неэффективно.

Профилирование позволяет ответить на ключевые вопросы:

  • Какие столбцы чаще всего участвуют в условиях WHERE?
  • Какие комбинации полей используются в ORDER BY и GROUP BY?
  • Какие запросы возвращают большое количество строк, но фильтруют по редким значениям?
  • Какие запросы выполняются наиболее часто?

Ответы на эти вопросы формируют основу для принятия решений о создании новых индексов или удалении существующих.

Баланс между чтением и записью​

Каждый индекс ускоряет операции чтения, но замедляет операции записи. При вставке новой строки СУБД должна обновить все связанные с ней индексы. При обновлении значений в проиндексированных столбцах требуется перестроение соответствующих записей в индексе. При удалении строки — удаление из всех индексов.

Поэтому в системах с преобладанием операций записи (например, в системах логирования или IoT-платформах) число индексов стремятся минимизировать. В то же время в аналитических системах, где большинство запросов — это сложные выборки, количество индексов может быть значительно больше.

Архитектурные паттерны, такие как CQRS (Command Query Responsibility Segregation), позволяют разделить модель записи и модель чтения. В этом случае таблица для записи может иметь минимальный набор индексов, а отдельная реплика или материализованное представление — богатый набор индексов, оптимизированных под запросы.

Индексирование в распределённых системах​

В распределённых базах данных (например, Cassandra, ScyllaDB, CockroachDB) стратегия индексирования усложняется. Здесь важно учитывать не только локальную структуру данных, но и способ их распределения по узлам кластера.

В таких системах первичный ключ часто состоит из двух частей: партиционного ключа и кластеризованного ключа. Партиционный ключ определяет, на каком узле будет храниться строка, а кластеризованный — порядок строк внутри партиции. Эффективное проектирование первичного ключа заменяет собой необходимость в дополнительных индексах, поскольку данные уже упорядочены нужным образом.

Вторичные индексы в распределённых системах могут быть глобальными или локальными. Глобальные индексы требуют координации между узлами и могут снижать производительность записи. Локальные индексы ограничены одной партицией и работают быстрее, но не позволяют эффективно выполнять запросы, охватывающие несколько партиций.

Поэтому в распределённых системах предпочтение отдаётся проектированию схемы данных под конкретные запросы, а не созданию универсальных индексов.

Автоматическое и рекомендательное индексирование​

Современные облачные СУБД (например, Amazon Aurora, Azure SQL Database, Google Cloud Spanner) предлагают функции автоматического создания индексов на основе анализа рабочей нагрузки. Эти системы отслеживают частоту запросов, их стоимость и потенциальный выигрыш от индекса, после чего предлагают или автоматически применяют рекомендации.

Однако полностью полагаться на автоматику не стоит. Автоматические рекомендации могут не учитывать долгосрочные последствия, например, рост объёма данных или изменение паттернов использования. Кроме того, они могут создавать избыточные индексы, если запросы временно меняют свой характер.

Лучшая практика — использовать автоматические рекомендации как отправную точку, но принимать окончательные решения вручную, с учётом архитектурных целей и бизнес-контекста.

Управление жизненным циклом индексов​

Индексы, как и любой другой элемент системы, требуют управления на протяжении всего жизненного цикла приложения. Это включает:

  • Мониторинг использования: регулярная проверка, какие индексы реально используются, а какие — нет.
  • Удаление неиспользуемых индексов: неиспользуемые индексы занимают место на диске и замедляют операции записи без какой-либо пользы.
  • Реиндексация и дефрагментация: со временем индексы фрагментируются, особенно при частых обновлениях. Периодическая перестройка индексов восстанавливает их эффективность.
  • Адаптация к изменениям: при изменении бизнес-логики или добавлении новых функций могут потребоваться новые индексы или изменение существующих.

Такой подход обеспечивает не только высокую производительность, но и экономическую эффективность, поскольку каждый индекс — это дополнительные затраты на хранение и обслуживание.

Индексирование в типичных бэкенд-сценариях​

В повседневной практике разработки бэкенд-систем встречаются повторяющиеся паттерны запросов, для которых существуют проверенные стратегии индексирования. Ниже приведены несколько распространённых случаев и подходы к ним.

Фильтрация по диапазону дат и статусу
Многие приложения работают с событиями, заказами, логами или транзакциями, где часто требуется выбрать записи за определённый период и с конкретным статусом. Например:

SELECT * FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31'
  AND status = 'completed';

Для такого запроса эффективен многостолбцовый индекс, в котором сначала идёт столбец с высокой селективностью (например, status), а затем — столбец диапазона (created_at). Это позволяет быстро найти все строки со статусом «completed», а затем отфильтровать их по дате без полного сканирования.

Сортировка с пагинацией
При реализации пагинации часто используется конструкция ORDER BY ... LIMIT ... OFFSET. Если порядок сортировки не поддерживается индексом, СУБД вынуждена сортировать весь результат перед применением LIMIT, что становится крайне медленным при росте объёма данных. Индекс по полям сортировки позволяет избежать этой операции.

Особое внимание требуется при смешанной сортировке (например, ORDER BY created_at DESC, id ASC). В таких случаях индекс должен точно соответствовать направлению сортировки каждого столбца.

Поиск по частичному совпадению
Запросы вида LIKE '%текст%' не могут использовать стандартные B-деревья. Для ускорения таких операций применяются специализированные индексы: полнотекстовые (в PostgreSQL, MySQL), GIN/GiST (в PostgreSQL), или сторонние решения вроде Elasticsearch. Важно понимать, что такие индексы требуют дополнительных ресурсов и усложняют архитектуру.

Агрегация по нескольким измерениям
В аналитических системах часто встречаются запросы с GROUP BY по комбинации полей. Например, группировка продаж по региону, категории товара и месяцу. Здесь помогает индекс по всем полям группировки, особенно если он покрывает также агрегируемые столбцы (например, сумму продаж).

Учёт специфики СУБД​

Каждая система управления базами данных имеет свои особенности в реализации индексов. Например:

  • PostgreSQL поддерживает выражения в индексах (CREATE INDEX ON orders ((customer_id || '-' || status))), частичные индексы (WHERE status = 'active') и расширенные типы индексов (GIN, GiST, SP-GiST, BRIN).
  • MySQL использует индексы для покрытия только в том случае, если все запрашиваемые столбцы входят в индекс. В InnoDB первичный ключ автоматически включается в каждый вторичный индекс.
  • Microsoft SQL Server предлагает фильтруемые индексы и возможность указывать параметры сжатия, что снижает размер индекса и ускоряет сканирование.
  • Oracle поддерживает инвертированные индексы, функциональные индексы и bitmap-индексы, которые особенно эффективны в хранилищах данных.

Понимание этих особенностей позволяет проектировать более точные и экономичные индексы.